package cn.edu.swu.ws.db;

import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class DatabaseService {

    private BasicDataSource dataSource = null;

    private static DatabaseService instance = new DatabaseService();
    public final static String CONTEXT_KEY = "DATABASE_SERVICE";

    private DatabaseService(){
    }

    public static DatabaseService getInstance() {
        return instance;
    }

    public void init() {
        System.out.println("🚀 开始初始化数据库连接...");

        try {
            dataSource = new BasicDataSource();
            dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

            // 测试不同的连接方案
            String url = "jdbc:mysql://10.122.7.154:3306/book_store_2025" +
                    "?useSSL=false" +
                    "&allowPublicKeyRetrieval=true" +
                    "&serverTimezone=Asia/Shanghai" +
                    "&connectTimeout=5000" +
                    "&socketTimeout=30000" +
                    "&characterEncoding=UTF-8";

            System.out.println("📡 连接URL: " + url);
            System.out.println("👤 用户名: root");

            dataSource.setUrl(url);
            dataSource.setUsername("root");
            dataSource.setPassword("qwer1234");

            // 简化连接池配置
            dataSource.setInitialSize(1);
            dataSource.setMaxTotal(2);
            dataSource.setMaxWaitMillis(10000);
            dataSource.setValidationQuery("SELECT 1");

            // 立即测试连接
            System.out.println("🔍 测试数据库连接...");
            try (Connection conn = dataSource.getConnection()) {
                System.out.println("✅ 数据库连接测试成功！");

                // 测试查询
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SELECT DATABASE(), USER()")) {
                    if (rs.next()) {
                        System.out.println("📊 当前数据库: " + rs.getString(1));
                        System.out.println("👤 当前用户: " + rs.getString(2));
                    }
                }
            }

            System.out.println("🎉 数据库服务初始化完成！");

        } catch (Exception e) {
            System.out.println("❌ 数据库初始化失败！");
            System.out.println("💥 错误类型: " + e.getClass().getName());
            System.out.println("📝 错误信息: " + e.getMessage());
            e.printStackTrace();

            // 重新抛出异常，让应用启动失败
            throw new RuntimeException("数据库初始化失败", e);
        }
    }

    /**
     * 关闭数据源连接池
     */
    public void closeDataSource() {
        if (dataSource != null) {
            try {
                dataSource.close();
                System.out.println("✅ 数据库连接池已关闭");
            } catch (SQLException e) {
                System.out.println("⚠️ 关闭数据库连接池时出现警告: " + e.getMessage());
                // 不抛出异常，因为这是关闭操作
            }
        }
    }

    public List query(String sql, ResulSetVisitor visitor) throws SQLException {
        try(Connection connection = this.dataSource.getConnection()) {
            try(Statement statement = connection.createStatement()) {
                try(ResultSet resultSet = statement.executeQuery(sql)) {
                    return visitor.visit(resultSet);
                }
            }
        }
    }

    public boolean execute(String sql) throws SQLException {
        try(Connection connection = this.dataSource.getConnection()) {
            try(Statement statement = connection.createStatement()) {
                return statement.execute(sql);
            }
        }
    }
}